/*
 * Copyright (C) Francisco Morero Peyrona. All rights reserved.
 * 
 * This software is published under the terms of Open Source License version 1.1, a copy of which has been included with this
 * distribution in the License.txt file.
 */

package jdbcmanager.dbtools;

import java.util.*;
import java.sql.*;

import java.awt.*;
import java.awt.event.*;
import javax.swing.*;

import jdbcmanager.swing.*;
import jdbcmanager.swing.text.*;
import jdbcmanager.app.*;
import jdbcmanager.dbtools.agentwise.*;
import jdbcmanager.dbtools.dbconnect.*;

/**
 * Contains a set of functions that deal with common DBMS actions.
 * <p>
 * Also provides access to MetaData and SQL Commands services via its internal instance of AgentDBMS.
 * 
 * @author Francisco Morero Peyrona
 */
public class DbUtil // Can't be final because some AgentMetaData or AgentSQL could be interested in extending this class
{
	private static Hashtable			htConnPool = new Hashtable();
	private static AgentDBMS			agent	   = null;
	private static ConnectionDefinition	connDef	   = null;

	//-------------------------------------------------------------------------//

	/**
	 * Stablishes a connection (based on information stored in the passed parameter).
	 * <p>
	 * If passed parameter is <code>null</code>, then the Connection dialog will be opened.
	 * 
	 * @param cd
	 *            An instance of ConnectionDefinition or <code>null</code>
	 * @return <code>true</code> if the connection was successfuly stablished
	 */
	public static boolean connect( ConnectionDefinition cd )
	{
		if( cd == null )
		{
			JConnectDlg dialog = new JConnectDlg();
			dialog.center();
			dialog.setVisible( true );

			if( dialog.getCloseOption() == JxDialog.ACCEPTED )
				cd = dialog.getConnectionDefinition();
		}

		return (cd == null) ? false : _connect( cd );
	}

	/**
	 * Connects with a catalog (asking for user and password only the first time).
	 * <p>
	 * Note: <br>
	 * An internal HashTable in this class stores one connection per catalog (DataBase) existing in the driver (server). <br>
	 * There is only one <code>AgentMetaData</code> in the whole driver (server), but there is needed one connection per DataBase:
	 * this connection can be used to access all tables inside this DataBase.
	 * 
	 * @param sCatalog
	 *            DataBase to connect with (can't be <code>null</code>)
	 * @return A connection to the passed catalog or <code>null</code> if the user has not rights to access it or an exception
	 *         occurred.
	 */
	public static Connection getConnection( String sCatalog )
	{
	    Connection conn = (Connection) DbUtil.htConnPool.get( (sCatalog == null) ? "null" : sCatalog );

		if( conn == null && connDef != null )
		{
			// First try with Initial Connection parameters
			try
			{
				conn = DbUtil.agent.getConnection( connDef.getURL(), sCatalog, connDef.getUser(), connDef.getPassword() );

				if( conn != null )
					DbUtil.htConnPool.put( (sCatalog == null) ? "null" : sCatalog, conn );
			}
			catch( Exception exc )
			{
				App.showDebugException( exc );
			}
            
			// If Inital Connection parameters did not work, open a dialog
			if( conn == null )
			{
				JTextField     txtUser = new JTextField();
				JPasswordField txtPass = new JPasswordField();
				JPanel         pnlLog  = new JPanel( new GridLayout( 2, 2, 6, 16 ) );
                
				pnlLog.setBorder( BorderFactory.createEmptyBorder( 8, 8, 8, 8 ) );
				pnlLog.add( new JLabel( "User" ) );
				pnlLog.add( txtUser );
				pnlLog.add( new JLabel( "Password" ) );
				pnlLog.add( txtPass );

				int nOption = JOptionPane.showConfirmDialog( App.getFrameMain(), pnlLog, "Connecting with '" + sCatalog + '\'', 
                                  JOptionPane.OK_CANCEL_OPTION,	JOptionPane.QUESTION_MESSAGE );

				if( nOption == JOptionPane.OK_OPTION )
				{
					String sUser = txtUser.getText(); // Can't make a .trim() because there could be a DBMS that accepts " "
					String sPass = new String( txtPass.getPassword() );

					try
					{
						conn = DbUtil.agent.getConnection( connDef.getURL(), sCatalog, sUser, sPass );

						if( conn != null )
							DbUtil.htConnPool.put( sCatalog, conn );
					}
					catch( Exception exc )
					{
						conn = null;
						App.showMessage( "Error accessing: '" + sCatalog + "'", "Please, verify user and password and try again." );
						App.showDebugException( exc );
					}
				}
			}
		}

		return conn;
	}

	/**
	 * Executes an SQL command and optionally let the user to modify it before executed.
	 * <p>
	 * If <code>bAllowModify == true</code>, a dialog will show the SQL command and the user 
	 * can modify it before is sent to the DBMS.
	 * 
	 * @param stmt          Statement to be used to execute the SQL command
	 * @param sSQL          SQL command to execute
	 * @param bAllowModify  Can the user modofy the command?
	 * @return A <code>ResultSet</code> if passed command can return it, otherwise returns <code>null</code>
	 * @throws SQLException When something goes wrong
	 */
	public static ResultSet executeSQL( Statement stmt, String sSQL, boolean bAllowModify ) throws SQLException, InterruptedException
	{
		Boolean bShowModifyDlg = (Boolean) App.getSessionProperty( "SHOW_SQL_CONFIRMATION_DIALOG" );

		if( bAllowModify && (bShowModifyDlg == null || bShowModifyDlg.booleanValue()) )
		{
			JModifySqlCommandDlg editor = new JModifySqlCommandDlg( sSQL );

			if( editor.getCloseOption() != JxDialog.ACCEPTED )
				throw new InterruptedException( "Command execution interrupted by user." );

			sSQL = editor.getSqlCommand();
		}

		//-----------------------------------------------------------//

		stmt.setQueryTimeout( App.props().getConnQueryTimeOut( 0 ) );

		long nTime = System.currentTimeMillis();
		boolean bRS = stmt.execute( sSQL );

		App.getFrameMain().sqlVerbose( stmt.getConnection().getCatalog(), sSQL, System.currentTimeMillis() - nTime );

		return bRS ? stmt.getResultSet() : null;
	}

	/**
	 * Agent for selecetd Driver when connection was stablished with the DBMS.
	 * 
	 * @return Agent for selecetd Driver when connection was stablished with the DBMS.
	 */
	public static AgentDBMS getAgentDBMS()
	{
		return DbUtil.agent;
	}

	/**
	 * Retrieves the user name used to connect with the DBMS
	 * 
	 * @return The user name used to connect with the DBMS
	 */
	public static String getUser()
	{
		return DbUtil.connDef.getUser();
	}

	/**
	 * Retrieves the password used to connect with the DBMS
	 * 
	 * @return The Password used to connect with the DBMS
	 */
	public static String getPassword()
	{
		return DbUtil.connDef.getPassword();
	}

	/**
	 * URL selected when connection was stablished with the DBMS.
	 * 
	 * @return URL selected when connection was stablished with the DBMS.
	 */
	public static String getURL()
	{
		return DbUtil.connDef.getURL();
	}

	//-------------------------------------------------------------------------//

	// Called by this.connect( cd )
	private static boolean _connect( ConnectionDefinition cd )
	{
		App.setCursor( Cursor.WAIT_CURSOR );
		
		Connection conn = null;
		AgentDBMS agent = AgentDBMS.getAgent4DriverId( cd.getDriverId() );

		if( agent != null )
		{
			String sWndTitle = App.getFrameMain().getTitle();
            App.getFrameMain().setTitle( "Connecting..." );

			try
			{
				conn = agent.initDriver( cd.getURL(), cd.getUser(), cd.getPassword() );

				if( conn == null )
				{
					App.showMessage( "Error connecting with \"" + cd.getURL() + "\"" );
				}
				else
				{
					String sCatalog = conn.getCatalog();

					DbUtil.agent = agent;
					DbUtil.connDef = cd;
					DbUtil.connDef.setCatalog( sCatalog );

					// Connections pool must be cleared (conn are automatically closed by the gc)
					DbUtil.htConnPool.clear();

					// Add new connection to pool (if catalog == null it is assumed that there is only 1 catalog)
					DbUtil.htConnPool.put( (sCatalog == null) ? "null" : sCatalog, conn );
				}
			}
			catch( SQLException exc )
			{
				String sMsg = "Can not connect with \"" + cd.getURL() + "\"";

				if( cd.getPassword() == null || cd.getPassword().length() == 0 )
					sMsg += "\nThe cause could be that provided password is empty.\n" + "Try again using a non empty password.";
				else
					sMsg += "\nPlease verify:\n" + "   * User and password\n" + "   * Connection syntax\n" + "   * Server is running on requested URL and port";

				App.peekCursor();
				App.showMessage( "Error connecting with DBMS", sMsg );
			}
			catch( ClassNotFoundException exc )
			{
				App.peekCursor();
				App.showException( exc, "Driver could not be found." );
			}
			catch( Exception exc )
			{
				App.peekCursor();
				App.showException( exc, "Error loading driver." );
			}
			finally
			{
                App.getFrameMain().setTitle( sWndTitle );
			}
		}
		else
		{
			App.showMessage( "Error loading driver", "There is no driver with signature = '" + cd.getDriverId() + '\'' );
		}

		App.restoreCursor();

		return conn != null;
	}

	//-------------------------------------------------------------------------//
	// INNER CLASS: JModifySqlCommandDlg
	//-------------------------------------------------------------------------//
	// SQL Command Editor: A dialog that shows the SQL command before it is sent to the
	// the DBMS letting the user can modify it if he wants.
	// It is useful to add special clauses to commands like "CREATE TABLE..." or "INDEX ON..."
	private static final class JModifySqlCommandDlg extends JxDialog implements ActionListener
	{
		private JPanel			pnlCenter	= new JPanel( new BorderLayout( 10, 10 ) );
		private JSQLCodeEditor	pnlEditor	= new JSQLCodeEditor();
		private JCheckBox		chkNotAsk	= new JCheckBox( "Do not show this dialog again during this session" );

		private JModifySqlCommandDlg( String sSQL )
		{
			super( "SQL Editor (You can modify before execution)" );

			this.pnlEditor.setText( sSQL );
			this.chkNotAsk.setFont( new Font( "Dilog", Font.PLAIN, 10 ) );
			this.chkNotAsk.addActionListener( new ActionListener()
			{
				public void actionPerformed( ActionEvent evt )
				{
					App.setSessionProperty( "SHOW_SQL_CONFIRMATION_DIALOG", new Boolean( !chkNotAsk.isEnabled() ) );
				}
			} );

			this.pnlCenter.add( this.pnlEditor, BorderLayout.CENTER );
			this.pnlCenter.add( this.chkNotAsk, BorderLayout.SOUTH );

			getContentPane().add( this.pnlCenter, BorderLayout.CENTER );

			pack( 0, -240 );
			center();
			setVisible( true );
		}

		public void actionPerformed( ActionEvent evt )
		{
			onAccept( evt );
		}

		public String getSqlCommand()
		{
			return this.pnlEditor.getText();
		}
	}
	// End of Inner Class
}